﻿using System;
using System.Windows.Forms;
using System.Diagnostics;
using System.Data;
using System.Collections;
using System.Xml;
using System.Drawing;
using System.ComponentModel;
using System.Globalization;

namespace DataUpload
{
    public partial class FrmMapping : Form
    {
        private MainForm _container = null;

        // contains mapping data
        internal SortedList maps;

        // contains parsed files (TextFile, DriverDrivenFile)
        internal SortedList parsedFiles;

        private ArrayList _requiredColumns;

        public FrmMapping()
        {
            InitializeComponent();
        }

        public FrmMapping(MainForm container)
        {
            InitializeComponent();
            _container = container;

            lstFiles.SmallImageList = _container.imgList;
            maps = new SortedList();
            parsedFiles = new SortedList();
        }

        private void FrmMapping_Load(object sender, EventArgs e)
        {
            pnlMapping.Visible = false;

            btnRefreshSource_Click(sender, e);
        }

        private void FrmMapping_VisibleChanged(object sender, EventArgs e)
        {
            if (this.Visible)
            {
                txtDatabase.Text = "Database: " + _container.frmConnect.dbc.Database;

                // Remove excess files.
                lstFiles.Items.Clear();
                for (int i = 0; i < _container.frmBaseFormat.lstFiles.Items.Count; i++)
                    lstFiles.Items.Add((ListViewItem)_container.frmBaseFormat.lstFiles.Items[i].Clone());

                if (lstFiles.Items.Count > 0)
                {
                    Application.DoEvents();
                    lstFiles.Items[0].Selected = true;
                }

                // Remove excess mapping controls and parsed files and datagridview controls.
                bool flag = false;
                if (maps.Count > 0)
                {
                    for (int i = 0; i < maps.Count; i++)
                    {
                        flag = false;
                        string fileName = maps.GetKey(i).ToString();
                        int j = 0;

                        while (flag == false && j < lstFiles.Items.Count)
                        {
                            if (lstFiles.Items[j].Tag.ToString() == fileName)
                                flag = true;
                            j++;
                        }

                        if (flag == false)
                        {
                            maps.RemoveAt(i);

                            if (parsedFiles.ContainsKey(fileName))
                                parsedFiles.Remove(fileName);
                            i--;
                        }
                    }
                }
            }
        }

        private void btnNext_Click(object sender, EventArgs e)
        {
            if (maps.Count != lstFiles.Items.Count)
            {
                foreach (ListViewItem item in lstFiles.Items)
                {
                    if (maps.ContainsKey(item.Tag) == false)
                    {
                        MessageBox.Show("Are you missing " + item.Tag.ToString());
                        return;
                    }
                }
            }

            for (int i = 0; i < lstFiles.Items.Count; i++)
            {
                string mapStatus = ((MappingCtrl)maps.GetByIndex(i)).IsValidMap;
                if (mapStatus != "ok")
                {
                    lstFiles.Items[i].Selected = true;
                    if (pnlMapping.Controls.Count > 0)
                        pnlMapping.Controls.RemoveAt(0);
                    pnlMapping.Controls.Add((MappingCtrl)maps[lstFiles.Items[i].Tag.ToString()]);
                    MessageBox.Show(mapStatus);
                    return;
                }
            }

            this.Hide();
            if (_container.frmProgress  == null)
                _container.frmProgress = new FrmProgress(_container);
            _container.frmProgress.Show();
            _container.frmProgress.Focus();
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            _container.Close();
        }

        private void btnPreview_Click(object sender, EventArgs e)
        {
            if (lstFiles.Items.Count == 0 || lstFiles.SelectedItems.Count == 0)
                return;

            try
            {
                Process.Start(lstFiles.SelectedItems[0].Tag.ToString());
            }
            catch (Exception exc)
            {
                DBConnection.MyDebugClass.LogThis("Try to open " + lstFiles.SelectedItems[0].Tag.ToString(), exc);
                MessageBox.Show("Please verify that this path existing: " + lstFiles.SelectedItems[0].Tag.ToString());
            }
        }

        private void btnBack_Click(object sender, EventArgs e)
        {
            this.Hide();
            _container.frmBaseFormat.Show();
            _container.frmBaseFormat.Focus();
        }

        private void btnReinspect_Click(object sender, EventArgs e)
        {
            if (lstFiles.Items.Count == 0 || lstFiles.SelectedItems.Count == 0)
                return;

            string selectingPath = lstFiles.SelectedItems[0].Tag.ToString();
            if (parsedFiles.ContainsKey(selectingPath))
                parsedFiles.Remove(selectingPath);
            else
                return;

            if (maps.ContainsKey(selectingPath))
                maps.Remove(selectingPath);
            if (pnlMapping.Controls.Count > 0)
                pnlMapping.Controls.RemoveAt(0);

            ParseFile(lstFiles.SelectedItems[0].Tag.ToString());
            lstFiles_SelectedIndexChanged(sender, e);
        }

        private void btnRefreshSource_Click(object sender, EventArgs e)
        {
            try
            {
                _container.frmConnect.dbc.Open();
                DataTable tb = _container.frmConnect.dbc.GetColumnInfo();
                _container.frmConnect.dbc.Close();
                dgvMappingField.Rows.Clear();
                _requiredColumns = new ArrayList();
                bool required;
                foreach (DataRow row in tb.Rows)
                {
                    required = true;

                    dgvMappingField.Rows.Add();
                    dgvMappingField[0, dgvMappingField.Rows.Count - 1].Value = row[0];

                    if (row[1].ToString().Equals("0", StringComparison.OrdinalIgnoreCase))
                        dgvMappingField[1, dgvMappingField.Rows.Count - 1].Value = false;
                    else
                    {
                        dgvMappingField[1, dgvMappingField.Rows.Count - 1].Value = true;
                        required = false;
                    }

                    if (row[2].ToString() == "" || row[2].ToString().Equals("null", StringComparison.OrdinalIgnoreCase))
                        dgvMappingField[2, dgvMappingField.Rows.Count - 1].Value = false;
                    else
                    {
                        dgvMappingField[2, dgvMappingField.Rows.Count - 1].Value = true;
                        required = false;
                    }

                    if (row[3].ToString().Equals("no", StringComparison.OrdinalIgnoreCase))
                        dgvMappingField[3, dgvMappingField.Rows.Count - 1].Value = false;
                    else
                    {
                        dgvMappingField[3, dgvMappingField.Rows.Count - 1].Value = true;
                        required = false;
                    }

                    switch (row[4].ToString())
                    {
                        case "nvarchar":
                        case "varchar":
                        case "nchar":
                        case "char":
                        case "text":
                        case "ntext":
                            dgvMappingField[4, dgvMappingField.Rows.Count - 1].Value = "string";
                            break;
                        case "int":
                        case "bigint":
                        case "smallint":
                        case "tinyint":
                        case "money":
                        case "smallmoney":
                        case "float":
                        case "real":
                        case "numeric":
                        case "decimal":
                            dgvMappingField[4, dgvMappingField.Rows.Count - 1].Value = "double";
                            break;
                        case "datetime":
                        case "smalldatetime":
                            dgvMappingField[4, dgvMappingField.Rows.Count - 1].Value = "DateTime";
                            break;
                        default:
                            dgvMappingField[4, dgvMappingField.Rows.Count - 1].Value = "unsupported";
                            break;
                    }

                    if (required == true)
                        _requiredColumns.Add(row[0]);
                }
            }
            catch (Exception exc)
            {
                DBConnection.MyDebugClass.LogThis("Attempting to get columns in " + _container.frmConnect.dbc.Database, exc);
                MessageBox.Show("Failed to get list of target columns");
            }
        }

        private void lstFiles_SelectedIndexChanged(object sender, EventArgs e)
        {
            Application.DoEvents();
            if (lstFiles.SelectedItems.Count > 0)
            {
                string selectingPath = lstFiles.SelectedItems[0].Tag.ToString();
                if (pnlMapping.Controls.Count > 0
                        && ((MappingCtrl)pnlMapping.Controls[0]).FilePath == selectingPath)
                    return;

                if (pnlMapping.Controls.Count > 0)
                    pnlMapping.Controls.RemoveAt(0);

                if (parsedFiles.Contains(selectingPath))
                {
                    if (!maps.Contains(selectingPath))
                        DisplayColumns(selectingPath);
                    pnlMapping.Controls.Add((MappingCtrl)maps[selectingPath]);
                }
                else
                {
                    ParseFile(selectingPath);
                    DisplayColumns(selectingPath);
                    pnlMapping.Controls.Add((MappingCtrl)maps[selectingPath]);
                }
            }
        }

        private void ParseFile(string selectingPath)
        {
            string action = "";
            try
            {
                //solve txt file
                if (selectingPath.EndsWith(".txt", StringComparison.OrdinalIgnoreCase)
                    || selectingPath.EndsWith(".csv", StringComparison.OrdinalIgnoreCase))
                {
                    action = "Prepare to parse " + selectingPath;
                    ReadFiles.TextFile readtxt = new ReadFiles.TextFile(selectingPath);

                    action = "Read saved format for file " + selectingPath;
                    UCBaseFormat ucBaseFormat = (UCBaseFormat)_container.frmBaseFormat.ucBaseFormat[selectingPath];
                    readtxt.RecordSeperator = ucBaseFormat.txtRowSep.Text;
                    readtxt.FieldSeperator = ucBaseFormat.txtColSep.Text;
                    readtxt.QuoteMark = ucBaseFormat.txtQuote.Text;
                    readtxt.HasTablesName = ucBaseFormat.ckcHasExplicitTable.Checked;

                    readtxt.FileCulture = new CultureInfo(ucBaseFormat.cboCulture.Text, false);
                    readtxt.NumberFormatInfo = readtxt.FileCulture.NumberFormat;
                    readtxt.NumberFormatInfo.NumberDecimalSeparator = ucBaseFormat.txtDecimal.Text;
                    readtxt.NumberFormatInfo.NumberGroupSeparator = ucBaseFormat.txtDigit.Text;
                    readtxt.DateTimeFormatInfo = readtxt.FileCulture.DateTimeFormat;
                    readtxt.DateTimeFormatInfo.ShortDatePattern = ucBaseFormat.txtDateFormat.Text;
                    readtxt.DateTimeFormatInfo.LongTimePattern = ucBaseFormat.txtTimeFormat.Text;

                    action = "Parsing file " + selectingPath;
                    readtxt.GetColumnsList();
                    parsedFiles.Add(selectingPath, readtxt);
                }
                else if (selectingPath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase)
                    || selectingPath.EndsWith(".mdb", StringComparison.OrdinalIgnoreCase)
                    || selectingPath.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase))
                {
                    action = "Prepare to parse " + selectingPath;
                    ReadFiles.DriverDrivenFile readdriver = new ReadFiles.DriverDrivenFile(selectingPath);

                    action = "Read saved format for file " + selectingPath;
                    UCBaseFormat ucBaseFormat = (UCBaseFormat)_container.frmBaseFormat.ucBaseFormat[selectingPath];
                    readdriver.FileCulture = new CultureInfo(ucBaseFormat.cboCulture.Text, false);
                    readdriver.NumberFormatInfo = readdriver.FileCulture.NumberFormat;
                    readdriver.NumberFormatInfo.NumberDecimalSeparator = ucBaseFormat.txtDecimal.Text;
                    readdriver.NumberFormatInfo.NumberGroupSeparator = ucBaseFormat.txtDigit.Text;
                    readdriver.DateTimeFormatInfo = readdriver.FileCulture.DateTimeFormat;
                    readdriver.DateTimeFormatInfo.ShortDatePattern = ucBaseFormat.txtDateFormat.Text;
                    readdriver.DateTimeFormatInfo.LongTimePattern = ucBaseFormat.txtTimeFormat.Text;

                    action = "Parsing file " + selectingPath;
                    readdriver.GetColumnsList();
                    parsedFiles.Add(selectingPath, readdriver);
                }
                else if (selectingPath.EndsWith(".mdf", StringComparison.OrdinalIgnoreCase))
                {
                    action = "Prepare to parse " + selectingPath;
                    ReadFiles.DriverDrivenFile readdriver;
                    try
                    {
                        readdriver = new ReadFiles.DriverDrivenFile(selectingPath);
                    }
                    catch (Exception exc)
                    {
                        DBConnection.MyDebugClass.LogThis(action, exc);
                        MessageBox.Show(exc.Message + Environment.NewLine + " The program now tries to connect automatically!");
                        string filePath = selectingPath;
                        if (filePath.Contains("\\"))
                            filePath = filePath.Substring(filePath.LastIndexOf("\\") + 1);
                        filePath = filePath.Substring(0, filePath.LastIndexOf("."));
                        string connString = "Provider=SQLNCLI.1;Initial Catalog=" + filePath + ";Integrated Security=SSPI;Data Source=";
                        connString += _container.frmConnect.dbc.Source;
                        readdriver = new DataUpload.ReadFiles.DriverDrivenFile(connString, true);
                    }

                    action = "Read saved format for file " + selectingPath;
                    UCBaseFormat ucBaseFormat = (UCBaseFormat)_container.frmBaseFormat.ucBaseFormat[selectingPath];
                    readdriver.FileCulture = new CultureInfo(ucBaseFormat.cboCulture.Text, false);
                    readdriver.NumberFormatInfo = readdriver.FileCulture.NumberFormat;
                    readdriver.NumberFormatInfo.NumberDecimalSeparator = ucBaseFormat.txtDecimal.Text;
                    readdriver.NumberFormatInfo.NumberGroupSeparator = ucBaseFormat.txtDigit.Text;
                    readdriver.DateTimeFormatInfo = readdriver.FileCulture.DateTimeFormat;
                    readdriver.DateTimeFormatInfo.ShortDatePattern = ucBaseFormat.txtDateFormat.Text;
                    readdriver.DateTimeFormatInfo.LongTimePattern = ucBaseFormat.txtTimeFormat.Text;

                    action = "Parsing file " + selectingPath;
                    readdriver.GetColumnsList();
                    parsedFiles.Add(selectingPath, readdriver);
                }
            }
            catch (Exception exc)
            {
                DBConnection.MyDebugClass.LogThis(action, exc);
                MessageBox.Show(exc.Message);
            }
        }

        private bool DisplayColumns(string selectingPath)
        {
            string action = "Display detected columns in " + selectingPath;
            try
            {
                MappingCtrl newMap = new MappingCtrl(selectingPath, _requiredColumns);
                maps.Add(selectingPath, newMap);

                ReadFiles.ParsedFile parsedFile = (ReadFiles.ParsedFile)parsedFiles[selectingPath];
                ArrayList columns = parsedFile.Columns;
                ListBox lv = (ListBox)(newMap.Controls["lboxField"]);
                lv.Items.Clear();
                for (int i = 0; i < columns.Count; i++)
                    lv.Items.Add(columns[i].ToString());
                return true;
            }
            catch (Exception exc)
            {
                DBConnection.MyDebugClass.LogThis(action, exc);
                MessageBox.Show("Error reading columns in " + selectingPath);
            }
            return false;
        }

        private void dgvMappingField_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            if (pnlMapping.Controls.Count > 0)
            {
                DataGridView dgvt = (DataGridView)(pnlMapping.Controls[0].Controls["dgvMapping"]);
                if (dgvt.CurrentRow != null && dgvt.CurrentRow.Cells[0].Value != null
                    && dgvMappingField[4, e.RowIndex].Value.ToString() != "unsupported"
                    && (bool)dgvMappingField[1, e.RowIndex].Value != true)
                {
                    dgvt.CurrentRow.Cells[1].Value = dgvMappingField[0, e.RowIndex].Value;
                    dgvt.CurrentRow.Cells[2].Value = dgvMappingField[4, e.RowIndex].Value;
                }
                dgvt.Focus();
            }
        }

        private void pnlMapping_ControlAdded(object sender, ControlEventArgs e)
        {
            pnlMapping.Visible = true;
            Application.DoEvents();
        }

        private void pnlMapping_ControlRemoved(object sender, ControlEventArgs e)
        {
            pnlMapping.Visible = false;
            Application.DoEvents();
        }
    }
}
